-- -------------------------------------------------
--    Project Name:  平台中断-订阅维度汇总数据
--    File Name:     jms_dm.dm_cn_breakoff_agg_reading_dt
--    Description:   计算（紫金山、逍遥峰）中心-中心、中心-集散、中心-网点超过24、48、72、96、120小时数据
--    Author:        Joyce | 利玉仙
--    date:          2021-12-02
-- -------------------------------------------------
INSERT OVERWRITE TABLE jms_dm.dm_cn_breakoff_agg_reading_dt PARTITION (dt)
SELECT network_code,
       first_value(network_name, TRUE)                                    AS network_name,      -- 责任机构名称
       first_value(agent_code, TRUE)                                      AS agent_code,        -- 代理区编码
       first_value(agent_name, TRUE)                                      AS agent_name,        -- 代理区名称
       first_value(franchisee_code, TRUE)                                 AS franchisee_code,   -- 加盟商编码
       first_value(franchisee_name, TRUE)                                 AS franchisee_name,   -- 加盟商名称
       order_source_code                                                  AS order_source_code,   -- 订单细分来源编码(抖音-紫金山: 1085, 快手-逍遥峰: 1133)
       first_value(order_source_name, TRUE)                               AS order_source_name,       -- 平台: 运单所属平台:OPPO电商, VIP系统, 拼多多-桃花岛, 抖音-紫金山, 快手-逍遥峰
       sum(1)                                                             AS breakoff_cnt,      -- 发生中断次数
       sum(if(breakoff_min >= 24 * 60 AND breakoff_min < 48 * 60, 1, 0))  AS breakoff_24h_cnt,  -- 中断超过 24 小时次数
       sum(if(breakoff_min >= 48 * 60 AND breakoff_min < 72 * 60, 1, 0))  AS breakoff_48h_cnt,  -- 中断超过 48 小时次数
       sum(if(breakoff_min >= 72 * 60 AND breakoff_min < 96 * 60, 1, 0))  AS breakoff_72h_cnt,  -- 中断超过 72 小时次数
       sum(if(breakoff_min >= 96 * 60 AND breakoff_min < 120 * 60, 1, 0)) AS breakoff_96h_cnt,  -- 中断超过 96 小时次数
       sum(if(breakoff_min >= 120 * 60, 1, 0))                            AS breakoff_120h_cnt, -- 中断超过 120 小时次数
       mage_region_code                                                   as mage_region_code,
       first_value(mage_region_name, TRUE)                                as mage_region_name,
       network_type                                                       as network_type,
       next_scan_type                                                     as next_scan_type,
       dt
FROM jms_dm.dm_cn_breakoff_detail_reading_dt
WHERE dt BETWEEN date_add('{{ execution_date | date_add(-1) | cst_ds }}', -10 + 2) AND '{{ execution_date | date_add(-1) | cst_ds }}' --  T-10 ~ T-1
GROUP BY network_code, order_source_code, mage_region_code, network_type, next_scan_type, dt
    DISTRIBUTE BY dt;
